//---------------------------------------------------------------------------- Package
const _ = require('lodash')
const nodeExcel = require('excel-export')
const xlsx = require('node-xlsx');
//---------------------------------------------------------------------------- Config
const { MYSQL } = require('../../config')
//---------------------------------------------------------------------------- Plugin
const mysql = require('../../plugin/util/mysql')
const sheet = require('../../plugin/util/sheet')
// ---------------------------------------------------------------------------- GET
exports.get = {
  /**
   * 获取公司注册列表
   */
  '/companyreg/list': async (ctx, next) => {

    let page = ctx.query.page
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);
    if(_.isUndefined(page)){
      page=1
    }
    page = (page < 1) ? 0 : (page-1)
    let num = 15
    let sql = 'select A.*,B.is_pay from reg_company as A left join cpaylogs as B on A.id=B.fk_reg_company_id '+where_sql+'  order by  created desc  limit ?,?'
    let arg = [parseInt(page) * num,num]
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },

  /**
   * 获取公司注册列表总条数(get参数区分)
   */
  '/get/companyreg/count':async(ctx,next) =>{
    let sql = ''
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);
    sql = 'select count(id) as countnum from reg_company';
    sql = sql+" "+where_sql;
    let info = await mysql.query(MYSQL.XIONGAN,[sql],[])
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },

  /**
   * 导出excel
   */
  '/get/excel/companyreg/info':async(ctx,next) =>{
    let sql = 'select * from reg_company order by created desc'
    let arg = []
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    for(let i of info[0]){
      i.created = new Date(i.created*1000).format('yyyy-MM-dd hh:mm:ss')
      i.time_check = new Date(i.time_check*1000).format('yyyy-MM-dd hh:mm:ss')
      if(i.islink == 1){
        i.islink = '是'
      }else{
        i.islink = '否'
      }
    }
    let conf={};
    conf.cols = [
      {caption:'公司名称',type:'string'},
      {caption:'注册人名称',type:'string'},
      {caption:'联系电话',type:'string'},
      {caption:'备注名称',type:'string'},
      {caption:'是否反馈',type:'string'},
      {caption:'发布时间',type:'string'},
      {caption:'反馈时间',type:'string'},
      {caption:'备注',type:'string'},
    ];
    conf.rows = []
    for(let i of info[0]){
      conf.rows.push([i.com_name,i.person_name,i.mobile,i.option_com_name,i.islink,i.created,i.time_check,i.remark])
    }
    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "companyreg.csv");
    ctx.body = data
    return
  },

  /**
   * 获取注册公司信息
   */
  '/companyreg/desinfo':async(ctx,next) =>{
    let id = ctx.query.id
    let sql = 'select * from reg_company_info where id=?';
    let info = await mysql.query(MYSQL.XIONGAN,[sql],[id])
    sheet[0].data = info[0][0]
    ctx.body = sheet[0]
    return 
  },




}
// ---------------------------------------------------------------------------- POST
exports.post = {



}
// ---------------------------------------------------------------------------- PUT
exports.put = {
  /**
   * 更新公司注册信息
   */
  '/update/companyreginfo':async (ctx,next) =>{
    let {id,islink,remark} = ctx.request.fields
    let time = Date.parse(new Date()).toString().substr(0,10)
    let sql = 'update reg_company set islink=?,time_check=?,remark=? where id=?'
    let arg = [islink,time,remark,id]
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = []
    ctx.body = sheet[0]
  },

  /**
   * 更新公司介绍信息
   */
  '/update/companyregdes':async (ctx,next) =>{
    let {id,title,content} = ctx.request.fields;
    let sql = 'update reg_company_info set title=?, content=? where id=?';
    await mysql.query(MYSQL.XIONGAN,[sql],[title, content, id])
    sheet[0].data = []
    ctx.body = sheet[0]
  },

  /**
   * 更新付款状态>>>>>>>>>>>>>>>>>>>>>>>>
   */
  '/update/companyregpay':async (ctx,next) =>{
    let id = ctx.request.fields.id;

    let data = await mysql.query(MYSQL.XIONGAN,['select * from cpaylogs where fk_reg_company_id=?'],[id]);

    if(data[0].length > 0){
      var info = data[0][0]
      if(info.is_pay == 1){
        await mysql.query(MYSQL.XIONGAN,['update cpaylogs set is_pay=0 where fk_reg_company_id=?'],[id])
      }else{
        await mysql.query(MYSQL.XIONGAN,['update cpaylogs set is_pay=1 where fk_reg_company_id=?'],[id])
      }
    }else{
      await mysql.query(MYSQL.XIONGAN,['insert into cpaylogs(fk_reg_company_id, is_pay, total_fee) values(?,?,?)'],[id, 1, 50000])
    }

    sheet[0].data = []
    ctx.body = sheet[0]
  },



}
// ---------------------------------------------------------------------------- DELETE
exports.delete = {
}

//起始时间返回where sql语句的
function time_sql_where(start_time, end_time){
  let where_sql = "";
  if(start_time && end_time){
    where_sql = "where created>"+SetTimenum(start_time)+" and "+"created<"+SetTimenum(end_time); 
  }else if(start_time){
    where_sql = "where created>"+SetTimenum(start_time); 
  }else if(end_time){
    where_sql = "where created<"+SetTimenum(end_time); 
  }
  //转化时间为时间戳
  function SetTimenum(time){
    var timenum = Date.parse(new Date(time.replace(/\T/g,' '))).toString().slice(0,10);;
    return timenum;
  };  
  return where_sql;
}

//关联查询的sql
function join_time_sql_where(start_time, end_time){
  let where_sql = "";
  if(start_time && end_time){
    where_sql = "where A.created>"+SetTimenum(start_time)+" and "+"A.created<"+SetTimenum(end_time); 
  }else if(start_time){
    where_sql = "where A.created>"+SetTimenum(start_time); 
  }else if(end_time){
    where_sql = "where A.created<"+SetTimenum(end_time); 
  }
  //转化时间为时间戳
  function SetTimenum(time){
    var timenum = Date.parse(new Date(time.replace(/\T/g,' '))).toString().slice(0,10);;
    return timenum;
  };  
  return where_sql;
}
